DROP TABLE IF EXISTS jms_ods.yl_tms_branch_time_effective;   
CREATE EXTERNAL TABLE jms_ods.yl_tms_branch_time_effective(   
 ID bigint comment "主键",  --NUMBER(11)
 LINE_ID bigint comment "线路ID",  --NUMBER(11)
 LINE_CODE string comment "线路编码",  --NVARCHAR2(30)
 LINE_NAME string comment "线路名称",  --NVARCHAR2(100)
 DEPART_NETWORK_CODE string comment "发车网点编码",  --NVARCHAR2(30)
 DEPART_NETWORK_NAME string comment "发车网点名称",  --NVARCHAR2(60)
 PARENT_NETWORK_CODE string comment "发车所属网点编码",  --NVARCHAR2(30)
 PARENT_NETWORK_NAME string comment "发车所属网点名称",  --NVARCHAR2(50)
 START_ATTRIBUTION_CODE string comment "发车网点所属代理区编码",  --NVARCHAR2(30)
 START_ATTRIBUTION_AREA string comment "发车网点所属代理区",  --NVARCHAR2(50)
 START_REGIONAL_ID bigint comment "发车网点所属大区id",  --NUMBER(11)
 START_REGIONAL_DESC string comment "发车网点所属大区",  --NVARCHAR2(50)
 PLANNED_DEPARTURE_TIME string comment "规划发车时间",  --NVARCHAR2(5)
 PLANNED_DELIVERY_TIME string comment "规划发件时间",  --NVARCHAR2(5)
 LATEST_COLLECT_TIME string comment "最晚揽件时间",  --NVARCHAR2(5)
 DEADLINE_SIGNING_TIME string comment "签收截止时间",  --NVARCHAR2(5)
 LATEST_WAREHOUSING_TIME string comment "最晚入仓时间",  --NVARCHAR2(5)
 LATEST_ARRIVAL_TIME string comment "最晚到车时间",  --NVARCHAR2(5)
 ARRIVE_NETWORK_CODE string comment "到车网点编码",  --NVARCHAR2(30)
 ARRIVE_NETWORK_NAME string comment "到车网点名称",  --NVARCHAR2(60)
 PLANNED_ARRIVAL_TIME string comment "规划到达时间",  --NVARCHAR2(5)
 SPEED double comment "时速(km/h)",  --NUMBER
 MILEAGE double comment "里程(km)",  --NUMBER
 END_PARENT_NETWORK_CODE string comment "到车所属网点编码",  --NVARCHAR2(30)
 END_PARENT_NETWORK_NAME string comment "到车所属网点名称",  --NVARCHAR2(50)
 END_ATTRIBUTION_CODE string comment "到车网点所属代理区编码",  --NVARCHAR2(30)
 END_ATTRIBUTION_AREA string comment "到车网点所属代理区",  --NVARCHAR2(50)
 END_REGIONAL_ID bigint comment "到车网点所属大区id",  --NUMBER(11)
 END_REGIONAL_DESC string comment "到车网点所属大区",  --NVARCHAR2(50)
 SPAN_DAYS_ARRIVE bigint comment "跨越天数_到车",  --NUMBER(11)
 SPAN_DAYS_SIGN bigint comment "跨越天数_签收",  --NUMBER(11)
 TOTAL_RUNTIME string comment "总运行时间(min)",  --NVARCHAR2(10)
 TIMES string comment "时长",  --NVARCHAR2(10)
 LINE_TYPE bigint comment "线路类型 (1集货交件、2散货派件)",  --NUMBER(11)
 APPLY_DAY string comment "适用工作日,以逗号隔开(1,2,3)",  --NVARCHAR2(64)
 EFFECTIVE_DATE timestamp comment "生效时间",  --DATE
 EXPIRATION_DATE timestamp comment "失效时间",  --DATE
 IS_ENABLE smallint comment "启用状态:1启用；2禁用",  --NUMBER(4)
 CREATE_BY bigint comment "创建人id",  --NUMBER(11)
 CREATE_BY_NAME string comment "创建人姓名",  --NVARCHAR2(20)
 CREATE_TIME timestamp comment "创建时间",  --DATE
 UPDATE_BY bigint comment "更新人id",  --NUMBER(11)
 UPDATE_BY_NAME string comment "更新人姓名",  --NVARCHAR2(20)
 UPDATE_TIME timestamp comment "更新时间",  --DATE
 REMARK string comment "备注",  --NVARCHAR2(80)
 SHIFT_CODE string comment "班次编码",  --NVARCHAR2(30)
 SHIFT tinyint comment "班次",  --NUMBER(2)
 INWAREHOUSE_ADD_TIME bigint comment "收件加时" , --NUMBER(11)
 IN_LINE_DAY  bigint comment "收件班次-支线衔接跨天"  ,  --NUMBER(2)
 OUT_LINE_DAY  bigint comment  "派件班次-支线衔接跨天" ,   --NUMBER(2)
 SPAN_DAYS_SHIFT  bigint comment "派件跨越天数(记录班次的跨越天数)"  --NUMBER(11)
 )     
 COMMENT '时效信息'    
 PARTITIONED BY (dt STRING COMMENT '更新/创建日期 (yyyy-MM-dd)') 
 STORED AS PARQUET   
 LOCATION '/dw/hive/jms_ods.db/external/yl_tms_branch_time_effective'    
 TBLPROPERTIES (    
 'discover.partitions'='false',   
 'parquet.column.index.access'='true'   
 );     
     
MSCK REPAIR TABLE jms_ods.yl_tms_branch_time_effective; 